module.exports = {
  getMenus: `select * from SystemMenu order by SortValue ASC, Id ASC`,

  getMenu: `select * from SystemMenu where id=@id`,

  addMenu: `INSERT INTO SystemMenu
    (Name, Url, ParentId, Icon, Operators, Visible, SortValue)
    VALUES(@name, @url, @parentId, @icon, @operators, @visible, @sortValue)`,

  updateMenu: `UPDATE SystemMenu
    SET
      Name=@name,
      Url=@url,
      ParentId=@parentId,
      Icon=@icon,
      Operators=@operators,
      Visible=@visible,
      SortValue=@sortValue
    WHERE Id=@id`,

  removeUserRights: 'DELETE from SystemUserMenu where UserId=@userId',

  addUserRight: `INSERT into SystemUserMenu values(@userId, @menuId, @operators)`,

  removeRoleRights: 'DELETE from SystemRoleMenu where RoleId=@roleId',

  addRoleRight: `INSERT INTO SystemRoleMenu(RoleId, MenuId, Operators) VALUES(@roleId, @menuId, @operators);`,

  getUserRights: `SELECT 
      a.*,
      case when b.MenuId is null then 0 else 1 end HaveRight,
      b.Operators HaveOperators
    from SystemMenu a
    left join SystemUserMenu b on b.MenuId = a.Id and b.UserId=@userId
    order by a.SortValue asc, a.Id asc`,

  getRoleRights: `SELECT 
      a.*,
      case when b.MenuId is null then 0 else 1 end HaveRight,
      b.Operators HaveOperators
    from SystemMenu a
    left join SystemRoleMenu b on b.MenuId = a.Id and b.RoleId=@roleId
    order by a.SortValue asc, a.Id asc;`,

  getUserFullRights: `with cte as(
      SELECT 
        a.Id,
        a.Name,
        a.Url,
        a.ParentId,
        a.Icon,
        a.Visible,
        a.SortValue,
        b.Operators
      from SystemMenu a 
      inner join SystemUserMenu b on b.MenuId = a.Id where b.UserId=@userId
      UNION 
      SELECT
        a1.Id,
        a1.Name,
        a1.Url,
        a1.ParentId,
        a1.Icon,
        a1.Visible,
        a1.SortValue,
        b1.Operators
      from SystemMenu a1
      inner join SystemRoleMenu b1 on b1.MenuId = a1.Id 
      INNER JOIN SystemUserRole c1 on c1.RoleId = b1.RoleId
      where c1.UserId=@userId
    ) 
    select
      Id,
      Name,
      Url,
      ParentId,
      Icon,
      Visible,
      SortValue,
      group_concat(Operators, ',') as Operators
    from cte
    group by Id
    order by SortValue asc, Id ASC`
}